package messy.test;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * @author admin
 *
 */
public class ExcelUtilClass {

	/**
	 * 读取一个Excel文件,并将第i列字段和第j列字段拼接在一起,赋值给第j列,并输出新文件
	 * @author jiaqi.han
	 * @date 2020年12月25日
	 */
	public void operatorExcel() throws IOException {
		XSSFWorkbook newWorkbook = new XSSFWorkbook();
		Sheet newSheet = newWorkbook.createSheet();

		String filePath = "e:\\file1.xlsx";
		InputStream is = new FileInputStream(filePath);
		XSSFWorkbook workbook = new XSSFWorkbook(is);
		// 循环读取表
		int sheetNum = workbook.getNumberOfSheets();
		for (int i = 0; i < sheetNum; i++) {
			Sheet sheet = workbook.getSheetAt(i);
			// 循环读取行
			int rowNum = sheet.getLastRowNum();
			for (int j = 0; j < rowNum + 1; j++) {
				Row newRow = newSheet.createRow(j);
				Row row = sheet.getRow(j);
				// 循环读取列
				int cellNum = row.getLastCellNum();
				for (int k = 0; k < cellNum; k++) {
					// 操作每一个单元格
					Cell newCell = newRow.createCell(k);
					Cell cell = row.getCell(k);
					if (k == 3 && j != 0) {
						String name = row.getCell(0).toString();
						String mindex = "";
						String newmindex = "";
						if (row.getCell(3) != null) {
							mindex = row.getCell(3).toString();
							newmindex = name + "," + mindex;
						} else {
							newmindex = name;
						}
						newCell.setCellValue(newmindex);
					} else {
						newCell.setCellValue(cell.toString());
					}
				}
			}
		}

		// 写出文件
		OutputStream os = new FileOutputStream("e:\\file2.xlsx");
		newWorkbook.write(os);
		os.close();
		is.close();
	}

}
